Declaring and Using Local Variables in MySQL

Course: MySQL Training Course

Topic: Declaring and Using Local Variables

Course Link: MySQL Training Course at JustAcademy

Demo Link: Register for a Course Demo

1. Introduction to Local Variables in MySQL

A local variable in MySQL is a named storage location used to temporarily hold a value while a stored procedure, stored function, or another supported stored-program block is executing. A local variable can store values such as numbers, text, dates, and calculated results.

Local variables are especially useful in stored procedures because they allow developers to perform calculations, store query results, check conditions, and control the execution of SQL statements without repeatedly writing the same expressions.

For example, a procedure that calculates the total sales amount can store the result in a local variable before displaying it to the user.

1.1 What Is a Local Variable?

A local variable is a variable declared inside a stored-program block using the DECLARE statement. Its scope is limited to the BEGIN ... END block in which it is declared and any accessible nested blocks, unless a nested block declares a variable with the same name.

DECLARE v_total INT DEFAULT 0;

In this example:

• DECLARE: Defines a local variable.
• v_total: The variable name.
• INT: The data type.
• DEFAULT 0: Sets its initial value to zero.

1.2 Why Are Local Variables Used?

• Store intermediate calculation results.
• Temporarily hold values retrieved from database tables.
• Count records returned by a query.
• Calculate totals, averages, and other summaries.
• Store values used in IF, ELSEIF, and ELSE conditions.
• Control loops and other procedural operations.
• Improve readability by separating calculations into clear steps.
• Avoid repeating complex expressions within a procedure.

1.3 Learning Objectives

After completing this topic, you should be able to:

• Understand local variables and their purpose.
• Declare variables using DECLARE.
• Choose suitable MySQL data types for variables.
• Initialize variables using DEFAULT.
• Assign values using SET.
• Retrieve query results using SELECT ... INTO.
• Use variables in calculations and conditional statements.
• Understand variable scope and common errors.
• Build practical stored procedures that use local variables.

2. Syntax for Declaring Local Variables

MySQL uses the DECLARE statement to define local variables inside a stored-program block.

2.1 Basic Syntax

DECLARE variable_name data_type [DEFAULT default_value];

Example:

DECLARE v_age INT DEFAULT 25;
DECLARE v_name VARCHAR(100) DEFAULT 'Aarav';
DECLARE v_salary DECIMAL(10,2) DEFAULT 35000.00;

2.2 Declaring a Variable Without a Default Value

A variable can be declared without a DEFAULT clause.

DECLARE v_count INT;

If no default is specified, the initial value of the local variable is NULL. If the variable is used in a calculation before receiving a value, the result may also be NULL.

For predictable behavior, initialize variables when a meaningful initial value is known.

2.3 Declaring a Variable with a Default Value

A DEFAULT clause assigns an initial value to the variable.

DECLARE v_count INT DEFAULT 0;
DECLARE v_status VARCHAR(20) DEFAULT 'Pending';
DECLARE v_amount DECIMAL(10,2) DEFAULT 0.00;

Default values are useful when a procedure needs a known starting value for a counter, total, status, or calculation.

2.4 Declaring Multiple Variables

Multiple variables of the same data type can be declared in one DECLARE statement. Variables of different data types are normally declared in separate statements.

DECLARE v_quantity, v_count INT DEFAULT 0;
DECLARE v_total DECIMAL(12,2) DEFAULT 0.00;
DECLARE v_product_name VARCHAR(100);

Each declaration must end with a semicolon. All local variable declarations must appear at the beginning of their applicable BEGIN ... END block, before executable statements and before cursor or handler declarations.

3. Where Local Variables Can Be Declared

Local variables must be declared within a supported stored-program block, such as the body of a stored procedure or stored function. They cannot be declared as ordinary local variables in a standalone SELECT statement.

3.1 Example Inside a Stored Procedure

DELIMITER //

CREATE PROCEDURE show_local_variable()
BEGIN
    DECLARE v_number INT DEFAULT 10;

    SELECT v_number AS number_value;
END //

DELIMITER ;

Execute the procedure:

CALL show_local_variable();

Expected output:

3.2 Why BEGIN and END Are Important

BEGIN and END define a compound statement block. The local variable is declared inside this block and can be referenced while the relevant block is active.

DELIMITER //

CREATE PROCEDURE calculate_example()
BEGIN
    DECLARE v_a INT DEFAULT 10;
    DECLARE v_b INT DEFAULT 20;
    DECLARE v_result INT DEFAULT 0;

    SET v_result = v_a + v_b;

    SELECT v_result AS result;
END //

DELIMITER ;

Expected output:

3.3 Declaration Order

MySQL requires declarations to appear in a particular order within a compound block.

1. Declare local variables and conditions.
2. Declare cursors, if needed.
3. Declare condition handlers, if needed.
4. Write executable SQL statements.

For example, a DECLARE statement placed after an executable SET statement in the same block can cause a syntax error.

4. Choosing Data Types for Local Variables

A local variable should use a data type appropriate for the value it stores. Selecting a suitable type improves clarity and helps prevent unexpected conversions or precision problems.

4.1 Using INT Variables

INT is commonly used for counters, identifiers, and whole-number calculations.

DELIMITER //

CREATE PROCEDURE demonstrate_int()
BEGIN
    DECLARE v_quantity INT DEFAULT 5;
    DECLARE v_bonus INT DEFAULT 2;

    SELECT v_quantity + v_bonus AS total_quantity;
END //

DELIMITER ;

4.2 Using DECIMAL Variables

DECIMAL is appropriate for financial amounts because it stores exact decimal values within its defined precision and scale.

DELIMITER //

CREATE PROCEDURE calculate_amount()
BEGIN
    DECLARE v_price DECIMAL(10,2) DEFAULT 250.50;
    DECLARE v_quantity INT DEFAULT 4;
    DECLARE v_total DECIMAL(12,2) DEFAULT 0.00;

    SET v_total = v_price * v_quantity;

    SELECT v_total AS total_amount;
END //

DELIMITER ;

Expected result: 1002.00.

4.3 Using VARCHAR Variables

VARCHAR is useful when a procedure needs to store names, categories, messages, or status values.

DELIMITER //

CREATE PROCEDURE demonstrate_text()
BEGIN
    DECLARE v_customer_name VARCHAR(100)
        DEFAULT 'Meera Patel';

    SELECT v_customer_name AS customer_name;
END //

DELIMITER ;

4.4 Using DATE Variables

DATE variables can hold dates used in reports and filtering operations.

DELIMITER //

CREATE PROCEDURE show_report_date()
BEGIN
    DECLARE v_report_date DATE DEFAULT '2026-01-31';

    SELECT v_report_date AS report_date;
END //

DELIMITER ;

5. Assigning Values with SET

The SET statement assigns a value or expression result to a local variable. A variable can be assigned an initial value through DEFAULT and then updated during procedure execution.

5.1 Basic SET Syntax

SET variable_name = expression;

Example:

DECLARE v_total INT DEFAULT 0;
SET v_total = 100;

After the SET statement executes, v_total contains 100.

5.2 Updating a Variable's Value

DELIMITER //

CREATE PROCEDURE update_local_value()
BEGIN
    DECLARE v_number INT DEFAULT 10;

    SET v_number = 25;

    SELECT v_number AS updated_value;
END //

DELIMITER ;

Expected output: 25.

5.3 Performing Arithmetic Calculations

SET can assign the result of an arithmetic expression to a variable.

DELIMITER //

CREATE PROCEDURE calculate_arithmetic()
BEGIN
    DECLARE v_a INT DEFAULT 20;
    DECLARE v_b INT DEFAULT 5;
    DECLARE v_sum INT DEFAULT 0;
    DECLARE v_difference INT DEFAULT 0;
    DECLARE v_product INT DEFAULT 0;
    DECLARE v_quotient DECIMAL(10,2) DEFAULT 0.00;

    SET v_sum = v_a + v_b;
    SET v_difference = v_a - v_b;
    SET v_product = v_a * v_b;
    SET v_quotient = v_a / v_b;

    SELECT
        v_sum AS sum_result,
        v_difference AS difference_result,
        v_product AS product_result,
        v_quotient AS quotient_result;
END //

DELIMITER ;

Expected results: sum 25, difference 15, product 100, and quotient 4.00.

5.4 Assigning Expressions

A local variable can store the result of an expression involving other variables.

DECLARE v_price DECIMAL(10,2) DEFAULT 500.00;
DECLARE v_tax_rate DECIMAL(5,2) DEFAULT 18.00;
DECLARE v_tax_amount DECIMAL(10,2) DEFAULT 0.00;

SET v_tax_amount = v_price * v_tax_rate / 100;

The calculated tax amount is 90.00. This is a mathematical example; actual tax rules depend on the applicable business and legal requirements.

6. Retrieving Query Results with SELECT INTO

The SELECT ... INTO statement retrieves query results and stores them in one or more local variables. It is particularly useful when a procedure needs to calculate a total, count records, or retrieve a specific field from a table.

6.1 Basic Syntax

SELECT expression
INTO variable_name
FROM table_name
WHERE condition;

The number and order of selected expressions must match the number and order of target variables.

6.2 Example: Count Employees

Assume an employees table exists. The following procedure stores the employee count in a local variable.

DELIMITER //

CREATE PROCEDURE count_employees_local()
BEGIN
    DECLARE v_employee_count INT DEFAULT 0;

    SELECT COUNT(*)
    INTO v_employee_count
    FROM employees;

    SELECT v_employee_count AS employee_count;
END //

DELIMITER ;

Execute it:

CALL count_employees_local();

COUNT(*) returns the number of rows, and SELECT ... INTO assigns that value to v_employee_count.

6.3 Example: Retrieve an Employee's Salary

DELIMITER //

CREATE PROCEDURE get_employee_salary(
    IN p_employee_id INT
)
BEGIN
    DECLARE v_salary DECIMAL(10,2) DEFAULT NULL;

    SELECT salary
    INTO v_salary
    FROM employees
    WHERE employee_id = p_employee_id;

    SELECT v_salary AS employee_salary;
END //

DELIMITER ;

Call the procedure:

CALL get_employee_salary(101);

If employee ID 101 exists, the procedure retrieves its salary. If no matching row exists, SELECT ... INTO raises a NOT FOUND condition; without a suitable handler, that condition can terminate the routine. A production procedure should handle missing records explicitly.

6.4 Example: Retrieve Multiple Column Values

Multiple columns can be assigned to multiple local variables in one SELECT ... INTO statement.

DELIMITER //

CREATE PROCEDURE get_employee_summary(
    IN p_employee_id INT
)
BEGIN
    DECLARE v_name VARCHAR(100);
    DECLARE v_department VARCHAR(100);
    DECLARE v_salary DECIMAL(10,2);

    SELECT employee_name, department, salary
    INTO v_name, v_department, v_salary
    FROM employees
    WHERE employee_id = p_employee_id;

    SELECT
        v_name AS employee_name,
        v_department AS department,
        v_salary AS salary;
END //

DELIMITER ;

6.5 Example: Calculate Total Sales

DELIMITER //

CREATE PROCEDURE calculate_total_sales_local()
BEGIN
    DECLARE v_total_sales DECIMAL(12,2) DEFAULT 0.00;

    SELECT COALESCE(SUM(order_amount), 0.00)
    INTO v_total_sales
    FROM orders;

    SELECT v_total_sales AS total_sales;
END //

DELIMITER ;

COALESCE ensures that the variable receives zero when there are no rows to sum, rather than NULL.

7. Using Local Variables with IF Statements

Local variables are frequently used in conditional logic. An IF statement checks a condition and executes different statements depending on whether the condition is true.

7.1 Basic IF Syntax

IF condition THEN
    statements;
END IF;

7.2 IF ... ELSE Example

DELIMITER //

CREATE PROCEDURE check_stock(
    IN p_quantity INT
)
BEGIN
    DECLARE v_message VARCHAR(50);

    IF p_quantity > 0 THEN
        SET v_message = 'Stock available';
    ELSE
        SET v_message = 'Stock unavailable';
    END IF;

    SELECT v_message AS stock_status;
END //

DELIMITER ;

Example calls:

CALL check_stock(10);
CALL check_stock(0);

7.3 IF ... ELSEIF ... ELSE Example

DELIMITER //

CREATE PROCEDURE classify_salary(
    IN p_salary DECIMAL(10,2)
)
BEGIN
    DECLARE v_category VARCHAR(30);

    IF p_salary >= 80000 THEN
        SET v_category = 'High';
    ELSEIF p_salary >= 40000 THEN
        SET v_category = 'Medium';
    ELSE
        SET v_category = 'Entry Level';
    END IF;

    SELECT v_category AS salary_category;
END //

DELIMITER ;

Example calls:

CALL classify_salary(90000);
CALL classify_salary(50000);
CALL classify_salary(25000);

These values are illustrative salary classifications. Real-world thresholds should follow the organization's requirements.

7.4 Important Note About NULL Values

Comparisons involving NULL generally produce an unknown result rather than TRUE or FALSE. When a variable may contain NULL, use IS NULL or IS NOT NULL to test it explicitly.

IF v_salary IS NULL THEN
    SET v_message = 'Salary is not available';
ELSE
    SET v_message = 'Salary is available';
END IF;

8. Using Local Variables with Loops

Local variables can act as counters or accumulators in loops. MySQL stored programs support constructs such as LOOP, WHILE, and REPEAT.

8.1 Example: WHILE Loop

DELIMITER //

CREATE PROCEDURE count_to_five()
BEGIN
    DECLARE v_counter INT DEFAULT 1;

    WHILE v_counter <= 5 DO
        SELECT v_counter AS current_number;
        SET v_counter = v_counter + 1;
    END WHILE;
END //

DELIMITER ;

Call the procedure:

CALL count_to_five();

The procedure executes the loop five times, producing the values 1 through 5. Depending on the client, each SELECT may appear as a separate result set.

8.2 Example: Calculate a Running Total

DELIMITER //

CREATE PROCEDURE calculate_running_total()
BEGIN
    DECLARE v_counter INT DEFAULT 1;
    DECLARE v_total INT DEFAULT 0;

    WHILE v_counter <= 5 DO
        SET v_total = v_total + v_counter;
        SET v_counter = v_counter + 1;
    END WHILE;

    SELECT v_total AS final_total;
END //

DELIMITER ;

Expected output: 15, calculated as 1 + 2 + 3 + 4 + 5.

8.3 Preventing Infinite Loops

• Initialize the loop counter before entering the loop.
• Define a condition that can eventually become false.
• Update the counter or relevant variable inside the loop.
• Test boundary values before using a loop with production data.
• Avoid running long loops when a set-based SQL query can solve the same problem more efficiently.

9. Local Variables and Nested Blocks

MySQL allows a compound block to contain another compound block. A local variable declared in an outer block can be used in a nested block. A variable declared in a nested block is available only within that nested block.

9.1 Example of a Nested Block

DELIMITER //

CREATE PROCEDURE demonstrate_nested_scope()
BEGIN
    DECLARE v_outer INT DEFAULT 100;

    BEGIN
        DECLARE v_inner INT DEFAULT 50;

        SELECT
            v_outer AS outer_value,
            v_inner AS inner_value;
    END;

    SELECT v_outer AS outer_value_after_block;
END //

DELIMITER ;

The nested block can access v_outer because it was declared in the outer block. The variable v_inner cannot be referenced after the nested block has ended.

9.2 Shadowing a Variable

If a nested block declares a local variable with the same name as an outer variable, the inner declaration takes precedence inside the nested block. This behavior is called variable shadowing.

DELIMITER //

CREATE PROCEDURE demonstrate_shadowing()
BEGIN
    DECLARE v_number INT DEFAULT 10;

    BEGIN
        DECLARE v_number INT DEFAULT 20;

        SELECT v_number AS inner_number;
    END;

    SELECT v_number AS outer_number;
END //

DELIMITER ;

The first result is 20 and the second result is 10. Although shadowing is supported, using distinct names is usually easier to understand and maintain.

10. Local Variables vs. User-Defined Variables

MySQL supports local variables inside stored programs and user-defined variables that begin with the @ character. They have different scopes and usage patterns.

10.1 Local Variable Example

DELIMITER //

CREATE PROCEDURE local_variable_example()
BEGIN
    DECLARE v_number INT DEFAULT 25;

    SELECT v_number AS result;
END //

DELIMITER ;

10.2 User-Defined Variable Example

SET @number = 25;
SELECT @number AS result;

The local variable is declared inside the procedure, while @number is a session-level user-defined variable. Use the correct variable type for the task.

11. Local Variables and Procedure Parameters

Local variables and procedure parameters both store values during procedure execution, but they serve different purposes.

11.1 Example Using a Parameter and a Local Variable

DELIMITER //

CREATE PROCEDURE calculate_discounted_price(
    IN p_price DECIMAL(10,2),
    IN p_discount_percent DECIMAL(5,2)
)
BEGIN
    DECLARE v_discount DECIMAL(10,2) DEFAULT 0.00;
    DECLARE v_final_price DECIMAL(10,2) DEFAULT 0.00;

    SET v_discount = p_price * p_discount_percent / 100;
    SET v_final_price = p_price - v_discount;

    SELECT
        p_price AS original_price,
        v_discount AS discount_amount,
        v_final_price AS final_price;
END //

DELIMITER ;

Call the procedure:

CALL calculate_discounted_price(1000.00, 10.00);

Expected values: original price 1000.00, discount amount 100.00, and final price 900.00.

12. Using Local Variables for Database Calculations

Local variables are commonly used to store values returned by aggregate functions and calculations involving table data.

12.1 Calculate Average Salary

DELIMITER //

CREATE PROCEDURE calculate_average_salary()
BEGIN
    DECLARE v_average_salary DECIMAL(12,2) DEFAULT NULL;

    SELECT AVG(salary)
    INTO v_average_salary
    FROM employees;

    SELECT v_average_salary AS average_salary;
END //

DELIMITER ;

AVG() calculates the average salary. If the table contains no non-NULL salary values, the result is NULL. If the procedure must display zero instead, use COALESCE around the aggregate result.

12.2 Count Products in a Category

DELIMITER //

CREATE PROCEDURE count_products_by_category(
    IN p_category VARCHAR(100)
)
BEGIN
    DECLARE v_product_count INT DEFAULT 0;

    SELECT COUNT(*)
    INTO v_product_count
    FROM products
    WHERE category = p_category;

    SELECT v_product_count AS product_count;
END //

DELIMITER ;

Call the procedure:

CALL count_products_by_category('Electronics');

12.3 Calculate Total Inventory Value

DELIMITER //

CREATE PROCEDURE calculate_inventory_value()
BEGIN
    DECLARE v_inventory_value DECIMAL(14,2) DEFAULT 0.00;

    SELECT COALESCE(SUM(quantity * unit_price), 0.00)
    INTO v_inventory_value
    FROM inventory;

    SELECT v_inventory_value AS inventory_value;
END //

DELIMITER ;

This procedure assumes that the inventory table has quantity and unit_price columns. The multiplication calculates the value of each row, and SUM() adds the values together.

13. Handling Missing Records and NULL Values

When using SELECT ... INTO, it is important to understand what happens when the query returns no rows or when a column contains NULL.

13.1 Missing Rows

If SELECT ... INTO returns no rows, MySQL raises a NOT FOUND condition. Without an appropriate handler, the procedure may stop before reaching later statements.

DELIMITER //

CREATE PROCEDURE find_employee_name(
    IN p_employee_id INT
)
BEGIN
    DECLARE v_name VARCHAR(100) DEFAULT NULL;

    BEGIN
        DECLARE CONTINUE HANDLER FOR NOT FOUND
            SET v_name = NULL;

        SELECT employee_name
        INTO v_name
        FROM employees
        WHERE employee_id = p_employee_id;
    END;

    IF v_name IS NULL THEN
        SELECT 'Employee not found or name is NULL' AS message;
    ELSE
        SELECT v_name AS employee_name;
    END IF;
END //

DELIMITER ;

This example handles the no-row condition. It also treats a NULL employee name like a missing record. If the application must distinguish these situations, use an additional variable to track whether a matching row was found.

13.2 NULL Values in Calculations

Arithmetic expressions involving NULL normally return NULL. For example, 100 + NULL produces NULL.

DECLARE v_a INT DEFAULT 100;
DECLARE v_b INT DEFAULT NULL;
DECLARE v_result INT DEFAULT 0;

SET v_result = v_a + v_b;

In this example, v_result becomes NULL. If a missing value should be treated as zero, use an appropriate expression such as:

SET v_result = v_a + COALESCE(v_b, 0);

14. Common Errors When Using Local Variables

14.1 Naming Variables Clearly

A local variable should not have the same name as a table column. MySQL can resolve an ambiguous identifier as a local variable rather than the intended column, which can lead to unexpected results.

Use prefixes such as v_ for local variables and p_ for procedure parameters.

DECLARE v_employee_name VARCHAR(100);

SELECT e.employee_name
INTO v_employee_name
FROM employees AS e
WHERE e.employee_id = p_employee_id;

The table alias e makes the source column explicit, while v_employee_name clearly identifies the local variable.

15. Best Practices for Local Variables

• Use descriptive variable names that explain their purpose.
• Choose suitable MySQL data types for the values being stored.
• Initialize variables with DEFAULT when an initial value is appropriate.
• Declare variables at the beginning of their compound block.
• Use a naming convention such as v_ for local variables.
• Avoid using the same name for a variable and a table column.
• Use SELECT ... INTO for retrieving query results into variables.
• Handle missing rows when using SELECT ... INTO for individual records.
• Use COALESCE when NULL should be replaced with a meaningful fallback value.
• Use DECIMAL for calculations that require exact decimal precision, such as financial amounts.
• Avoid unnecessary variables when a direct SQL expression is simpler.
• Test procedures with empty tables, missing IDs, NULL values, and boundary values.
• Return results through SELECT statements or OUT parameters when the caller needs them.
• Keep variable scope as narrow as practical.
• Review stored procedure logic before using it to modify important business data.

16. Practical Project: Employee Salary Analysis

16.1 Project Objective

Create a MySQL stored procedure that uses local variables to retrieve an employee's salary, calculate a bonus, and return the resulting salary information.

16.2 Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) NOT NULL
);

16.3 Insert Sample Records

INSERT INTO employees (employee_name, department, salary)
VALUES
('Aarav Sharma', 'IT', 55000.00),
('Meera Patel', 'HR', 45000.00),
('Rohan Verma', 'Finance', 60000.00),
('Priya Singh', 'IT', 65000.00);

16.4 Create the Salary Analysis Procedure

DELIMITER //

CREATE PROCEDURE analyze_employee_salary(
    IN p_employee_id INT
)
BEGIN
    DECLARE v_employee_name VARCHAR(100) DEFAULT NULL;
    DECLARE v_salary DECIMAL(10,2) DEFAULT NULL;
    DECLARE v_bonus DECIMAL(10,2) DEFAULT 0.00;
    DECLARE v_final_salary DECIMAL(12,2) DEFAULT 0.00;

    BEGIN
        DECLARE CONTINUE HANDLER FOR NOT FOUND
            SET v_employee_name = NULL;

        SELECT employee_name, salary
        INTO v_employee_name, v_salary
        FROM employees
        WHERE employee_id = p_employee_id;
    END;

    IF v_employee_name IS NULL OR v_salary IS NULL THEN
        SELECT 'Employee not found or salary is NULL' AS message;
    ELSE
        SET v_bonus = v_salary * 10 / 100;
        SET v_final_salary = v_salary + v_bonus;

        SELECT
            v_employee_name AS employee_name,
            v_salary AS current_salary,
            v_bonus AS bonus_amount,
            v_final_salary AS salary_with_bonus;
    END IF;
END //

DELIMITER ;

16.5 Execute the Procedure

CALL analyze_employee_salary(1);

For an employee earning 55000.00, the procedure calculates a bonus of 5500.00 and a salary-with-bonus amount of 60500.00.

16.6 Test a Missing Employee

CALL analyze_employee_salary(999);

If employee ID 999 does not exist, the procedure returns a message indicating that the employee could not be found or the salary is NULL.

16.7 Project Learning Outcomes

• Declare local variables using DECLARE.
• Initialize variables using DEFAULT.
• Retrieve table values using SELECT ... INTO.
• Calculate a bonus using SET.
• Use IF statements to check the retrieved values.
• Handle a missing record using a NOT FOUND handler.
• Return calculated results through a SELECT statement.

17. Practice Exercises

1. Declare an INT variable named v_count and initialize it to zero.
2. Declare a VARCHAR variable to store a customer name.
3. Create a procedure that adds two numbers using local variables.
4. Create a procedure that calculates the total price from quantity and unit price.
5. Create a procedure that counts the employees in a table.
6. Create a procedure that retrieves an employee's department using SELECT ... INTO.
7. Create a procedure that calculates average salary using a local variable.
8. Create a procedure that checks whether a product's stock quantity is greater than zero.
9. Create a procedure that calculates the sum of numbers from 1 to 10 using a WHILE loop.
10. Create a nested block with one outer variable and one inner variable.
11. Explain the difference between a local variable and a user-defined variable.
12. Handle the situation in which SELECT ... INTO finds no matching employee record.

18. Exercise Solutions

Solution 1: Declare a Counter

DECLARE v_count INT DEFAULT 0;

Solution 2: Declare a Customer Name

DECLARE v_customer_name VARCHAR(100) DEFAULT NULL;

Solution 3: Add Two Numbers

DELIMITER //

CREATE PROCEDURE add_two_numbers()
BEGIN
    DECLARE v_a INT DEFAULT 15;
    DECLARE v_b INT DEFAULT 25;
    DECLARE v_sum INT DEFAULT 0;

    SET v_sum = v_a + v_b;

    SELECT v_sum AS sum_result;
END //

DELIMITER ;

Solution 4: Calculate Total Price

DELIMITER //

CREATE PROCEDURE calculate_total_price()
BEGIN
    DECLARE v_quantity INT DEFAULT 4;
    DECLARE v_unit_price DECIMAL(10,2) DEFAULT 125.50;
    DECLARE v_total DECIMAL(12,2) DEFAULT 0.00;

    SET v_total = v_quantity * v_unit_price;

    SELECT v_total AS total_price;
END //

DELIMITER ;

Solution 5: Count Employees

DELIMITER //

CREATE PROCEDURE count_all_employees()
BEGIN
    DECLARE v_count INT DEFAULT 0;

    SELECT COUNT(*)
    INTO v_count
    FROM employees;

    SELECT v_count AS employee_count;
END //

DELIMITER ;

Solution 6: Retrieve an Employee Department

DELIMITER //

CREATE PROCEDURE get_employee_department(
    IN p_employee_id INT
)
BEGIN
    DECLARE v_department VARCHAR(100) DEFAULT NULL;

    SELECT department
    INTO v_department
    FROM employees
    WHERE employee_id = p_employee_id;

    SELECT v_department AS department_name;
END //

DELIMITER ;

For production use, add appropriate NOT FOUND handling if the employee ID might not exist.

Solution 7: Calculate Average Salary

DELIMITER //

CREATE PROCEDURE get_average_salary()
BEGIN
    DECLARE v_average_salary DECIMAL(12,2) DEFAULT NULL;

    SELECT AVG(salary)
    INTO v_average_salary
    FROM employees;

    SELECT v_average_salary AS average_salary;
END //

DELIMITER ;

Solution 8: Check Product Stock

DELIMITER //

CREATE PROCEDURE check_product_stock(
    IN p_quantity INT
)
BEGIN
    DECLARE v_stock_message VARCHAR(50);

    IF p_quantity > 0 THEN
        SET v_stock_message = 'Stock available';
    ELSE
        SET v_stock_message = 'Stock unavailable';
    END IF;

    SELECT v_stock_message AS stock_status;
END //

DELIMITER ;

Solution 9: Sum Numbers from 1 to 10

DELIMITER //

CREATE PROCEDURE sum_one_to_ten()
BEGIN
    DECLARE v_counter INT DEFAULT 1;
    DECLARE v_total INT DEFAULT 0;

    WHILE v_counter <= 10 DO
        SET v_total = v_total + v_counter;
        SET v_counter = v_counter + 1;
    END WHILE;

    SELECT v_total AS total_sum;
END //

DELIMITER ;

Expected result: 55.

Solution 10: Nested Variables

DELIMITER //

CREATE PROCEDURE nested_variable_example()
BEGIN
    DECLARE v_outer INT DEFAULT 100;

    BEGIN
        DECLARE v_inner INT DEFAULT 50;

        SELECT v_outer AS outer_value,
               v_inner AS inner_value;
    END;

    SELECT v_outer AS outer_value_after_block;
END //

DELIMITER ;

Solution 11: Local vs. User-Defined Variables

A local variable is declared using DECLARE inside a stored-program block and is available only within its scope. A user-defined variable starts with @ and is available within the current session.

Solution 12: Handle a Missing Employee

DELIMITER //

CREATE PROCEDURE safe_employee_lookup(
    IN p_employee_id INT
)
BEGIN
    DECLARE v_employee_name VARCHAR(100) DEFAULT NULL;
    DECLARE v_found BOOLEAN DEFAULT TRUE;

    BEGIN
        DECLARE CONTINUE HANDLER FOR NOT FOUND
            SET v_found = FALSE;

        SELECT employee_name
        INTO v_employee_name
        FROM employees
        WHERE employee_id = p_employee_id;
    END;

    IF v_found = FALSE THEN
        SELECT 'Employee not found' AS message;
    ELSE
        SELECT v_employee_name AS employee_name;
    END IF;
END //

DELIMITER ;

19. MySQL Interview Questions and Answers

Q1. What is a local variable in MySQL?

A local variable is a named storage location declared inside a stored-program block and used to hold temporary values during execution.

Q2. Which statement declares a local variable?

The DECLARE statement is used to declare local variables in stored programs.

Q3. What is the syntax for declaring a local variable?

DECLARE variable_name data_type [DEFAULT default_value];

Q4. Where can local variables be declared?

They can be declared within a supported stored-program compound block, such as the body of a stored procedure or function.

Q5. What happens if a local variable has no DEFAULT value?

Its initial value is NULL.

Q6. How do you assign a value to a local variable?

Use SET or retrieve a query result using SELECT ... INTO.

Q7. What is the purpose of SELECT ... INTO?

It assigns values returned by a query to one or more local variables.

Q8. What is the scope of a local variable?

Its scope is the BEGIN ... END block where it is declared and accessible nested blocks, subject to variable shadowing.

Q9. Can a local variable be used outside its procedure block?

No. Once its scope ends, it is no longer available. Use a result set or an OUT parameter to return a value to the caller.

Q10. What is the difference between a local variable and a user-defined variable?

A local variable is declared using DECLARE and has block scope. A user-defined variable begins with @ and has session scope.

Q11. Can multiple variables be declared in one statement?

Yes. Multiple variables of the same data type can be declared in one DECLARE statement.

Q12. Why should variable names differ from column names?

Matching names can create ambiguous references. MySQL may resolve an identifier as a local variable instead of the intended column.

Q13. Which data type is appropriate for currency calculations?

DECIMAL is generally suitable for exact decimal arithmetic used in financial calculations.

Q14. How can local variables be used in an IF statement?

A variable can store a value used in a condition, or it can hold the result assigned by different branches of an IF ... ELSE statement.

Q15. What happens if SELECT ... INTO returns no rows?

MySQL raises a NOT FOUND condition. Without an appropriate handler, the condition can interrupt procedure execution.

Q16. Can local variables be used in loops?

Yes. They can store counters, totals, and other values used by WHILE, REPEAT, and LOOP statements.

Q17. What is variable shadowing?

Variable shadowing occurs when a nested block declares a variable with the same name as an outer variable. The inner variable takes precedence within its block.

Q18. Why should local variables be initialized?

Initialization gives a variable a predictable starting value and helps prevent unexpected NULL results in calculations.

Q19. Can a local variable store the result of COUNT(*)?

Yes. Use SELECT COUNT(*) INTO variable_name FROM table_name.

Q20. What is the purpose of local variables in stored procedures?

They simplify calculations, store intermediate results, support conditions and loops, and make database logic easier to read and maintain.

20. Quick Revision Checklist

• DECLARE defines local variables in stored programs.
• Local variables must be declared at the beginning of their compound block.
• DEFAULT specifies an initial value.
• A variable without a default initially contains NULL.
• SET assigns values and expression results.
• SELECT ... INTO stores query results in variables.
• Use suitable data types for text, dates, counters, and decimal calculations.
• Local variables can be used with IF statements and loops.
• Variable scope is limited to the declaring block and accessible nested blocks.
• Avoid naming conflicts between local variables and table columns.
• Handle NOT FOUND conditions when queries may return no rows.
• Use COALESCE when NULL should be replaced by a fallback value.
• Use OUT parameters or result sets to return values to callers.
• Test procedures with missing records, NULL values, and boundary cases.

21. Conclusion

Declaring and using local variables is an essential skill for MySQL stored-program development. Local variables allow developers to store intermediate results, retrieve values from database tables, perform calculations, evaluate conditions, and control loops.

By mastering DECLARE, DEFAULT, SET, and SELECT ... INTO, you can build stored procedures that are more organized, reusable, and easier to maintain. Use appropriate data types, clear naming conventions, careful variable scope, and suitable error handling to make your database routines reliable.

Explore MySQL Training: Visit the JustAcademy MySQL Course

Book a Demo: Register for a JustAcademy Course Demo